package com.Action;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.io.FileUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.util.CellRangeAddress;

import com.jdbc.Jdbc;
import com.project.bean.Sales;

public class SalesOutput {
public SalesOutput() {
	
    
	
	//ContractTemplate ct =new ContractTemplate();
	
		//引入需要解析的EXCEL文件
	//String path = ct.filepath;
        String headline="销售信息";
		String[] title= {"学生编号","中文名","英文名","性别","出生日期","预约记录","是否到访","信息来源","市场推广员","课程顾问","电话课程顾问","分配状态","分配时间"};
		//创建EXCEL工作簿
			HSSFWorkbook workbook = new HSSFWorkbook();
			//创建一个工作sheet
			HSSFSheet sheet = workbook.createSheet();
			CellStyle cellStyle = workbook.createCellStyle();
			DataFormat format = workbook.createDataFormat();  
			cellStyle.setDataFormat(format.getFormat("@"));//默认为文本格式
			//创建第一行
			HSSFRow row = sheet.createRow(0);
			HSSFCell cell = null ;
			//创建标题
			sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 2));
			cell =row.createCell(0);
			cell.setCellValue(headline);
			//创建第二行
			HSSFRow rowtitle = sheet.createRow(1);
			//插入第二行title 
			for(int i=0;i<title.length;i++) {
			sheet.setDefaultColumnStyle(i, cellStyle);
			cell =rowtitle.createCell(i);
			cell.setCellValue(title[i]);
			}
			//追加数据,1先从数据库中获取结果集并且放到LIST中，然后一行一行地放入
			String find="select*from Sales ";
			  Jdbc j=new Jdbc();
			  ResultSet rs;
			  List<Sales> list=new ArrayList<>();
			 
			  try {
				j.Jdbc_Q2(find);
			} catch (ClassNotFoundException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			  rs=j.rs;
			  try {
				while(rs.next()){
					  Sales s=new Sales();
					  s.setId(rs.getString("id"));
					  s.setChinesename(rs.getString("chinesename"));
					  s.setEnglishname(rs.getString("englishname"));
					  s.setSex(rs.getString("sex"));
					  s.setBirthday(rs.getString("birthday"));
					  s.setReservation(rs.getString("reservation"));
					  s.setAttend(rs.getString("attend"));
					  s.setMessagefrom(rs.getString("messagefrom"));
					  s.setMarketman(rs.getString("marketman"));
					  s.setCounselor(rs.getString("counselor"));	  
					  s.setPhonecounselor(rs.getString("phonecounselor"));
					  s.setDistributionstate(rs.getString("distributionstate"));
					  s.setDistributiontime(rs.getString("distributiontime"));
					


					  
					  list.add(s);
					  }
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			  System.out.println(list.size());
			  
			int i=2 ;
				
				
				for(Sales s:list) {
				HSSFRow nextrow = sheet.createRow(i);
				HSSFCell cell2 = nextrow.createCell(0);
				cell2 .setCellValue(s.getId());
				cell2 = nextrow.createCell(1);
				cell2.setCellValue(s.getChinesename());
				cell2 = nextrow.createCell(2);
				cell2.setCellValue(s.getEnglishname());
				cell2 = nextrow.createCell(3);
				cell2.setCellValue(s.getSex());
				cell2 = nextrow.createCell(4);
				cell2.setCellValue(s.getBirthday());
				cell2 = nextrow.createCell(5);
				cell2.setCellValue(s.getReservation());
				cell2 = nextrow.createCell(6);
				cell2.setCellValue(s.getAttend());
				cell2 = nextrow.createCell(7);
				cell2.setCellValue(s.getMessagefrom());
				cell2 = nextrow.createCell(8);
				cell2.setCellValue(s.getMarketman());
				cell2 = nextrow.createCell(9);
				cell2.setCellValue(s.getCounselor());
				cell2 = nextrow.createCell(10);
				cell2.setCellValue(s.getPhonecounselor());
				cell2 = nextrow.createCell(11);
				cell2.setCellValue(s.getDistributionstate());
				cell2 = nextrow.createCell(12);
				cell2.setCellValue(s.getDistributiontime());
				
				i++;
				}
			
			//创建一个文件
			File file = new File("/Users/duyifan/Desktop/sales.xls");
			
			try {
				file.createNewFile();
				//将EXCELL内容存盘
				FileOutputStream stream = FileUtils.openOutputStream(file);
				workbook.write(stream);
				stream.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
	
}
}
